/*
 * Daniel R Padilla
 *
 * Copyright (c) 2008, Daniel R Padilla
 *
 * This copyrighted material is made available to anyone wishing to use, modify,
 * copy, or redistribute it subject to the terms and conditions of the GNU
 * Lesser General Public License, as published by the Free Software Foundation.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
 * or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU Lesser General Public License
 * for more details.
 *
 * You should have received a copy of the GNU Lesser General Public License
 * along with this distribution; if not, write to:
 * Free Software Foundation, Inc.
 * 51 Franklin Street, Fifth Floor
 * Boston, MA  02110-1301  USA
 */
package org.databasefacade.examples;

import org.databasefacade.DatabaseFacade;
import org.databasefacade.select.Comparison;
import org.databasefacade.statements.SelectStatement;
import org.databasefacade.table.column.Column;
import org.databasefacade.table.system.DbfFieldRegistryTable;
import org.databasefacade.table.system.DbfTableRegistryTable;

public class ExampleSelectStatements {
	public DbfTableRegistryTable tabTable;
	public DbfFieldRegistryTable colTable;
	
	/**
	 * Example: SELECT <table>.* 
	 *            FROM <table>
	 */
	public void example1(int translatorType) {
		// DatabaseFacade.translatorTestingInit(translatorType);	
		SelectStatement statement;
		statement = new SelectStatement(tabTable, true);
		System.out.println(statement.getSQL());
	}
	
	/**
	 * Example: SELECT <table1>.*, 
	 *                 <table2>.field
	 *            FROM <table1>
	 *                 INNER JOIN <table2>
	 *                   ON <table1>.<key> = <table2>.<foreign key>
	 */
	public void example2(int translatorType) {
		// DatabaseFacade.translatorTestingInit(translatorType);	
		SelectStatement statement;

		// select * from <table1>
		statement = new SelectStatement(tabTable, true);
		
		// Add <table2>.<field>
		statement.select().column(tabTable.getColumn(DbfTableRegistryTable.DATABASE_TABLE_NAME));
		
		// Join <table2>
		statement.from().join(tabTable);
		
		// Print results
		System.out.println(statement.getSQL());
	}
	
	/**
	 * Example: SELECT <alias1>.*, 
	 *                 <alias2>.field <field_alias1>
	 *                 <alias13>.field <field_alias2>
	 *            FROM <table1> alias1
	 *                 INNER JOIN <table2> alias2
	 *                   ON <alias1>.<key> = <alias2>.<foreign key>
	 *                 INNER JOIN <table3> alias3
	 *                   ON <alias1>.<key> = <alias3>.<foreign key>
	 *                      AND <alias3>.<db table name> = 'TableRegistry'
	 */
	public void example3(int translatorType) {
		// DatabaseFacade.translatorTestingInit(translatorType);	
		SelectStatement statement;
		
		// Setup table aliases		
		tabTable.setTableAlias("TR");
		colTable.setTableAlias("FR");
	
		// select * from FieldRegistry
		statement = new SelectStatement(colTable, true);
		
		// Add field alias1
		statement.select().column(tabTable.getColumn(DbfTableRegistryTable.DATABASE_TABLE_NAME), "MY_COLUMN_ALIAS");
		
		// Join Table2
		statement.from().join(tabTable);
		
		// Join Table3 and add field alias2
		tabTable.setTableAlias("TR2");
		statement.select().column(tabTable.getColumn(DbfTableRegistryTable.DATABASE_TABLE_NAME), "MY_OTHER_ALIAS");
		statement.from().join(tabTable).on().and().equalToValue(tabTable.getColumn(DbfTableRegistryTable.DATABASE_TABLE_NAME), "TableRegistry");

		// Print results
		System.out.println("\n\n" + statement.getSQL());
	}
	
	
	/**
	 * Example: SELECT <alias1>.*, 
	 *                 <alias2>.field <field_alias1>
	 *                 <alias13>.field <field_alias2>
	 *            FROM <table1> alias1
	 *                 LEFT OUTER JOIN <table2> alias2
	 *                   ON <alias1>.<key> = <alias2>.<foreign key>
	 *                 RIGHT OUTER JOIN <table3> alias3
	 *                   ON <alias1>.<key> = <alias3>.<foreign key>
	 *                 FULL OUTER JOIN <table4> alias4
	 *                   ON <alias1>.<key> = <alias4>.<foreign key>
	 *                 CROSS JOIN <table4> alias5
	 *                   ON <alias1>.<key> = <alias5>.<foreign key>
	 */
	public void example4(int translatorType) {
		// DatabaseFacade.translatorTestingInit(translatorType);	
		SelectStatement statement;
		
		// Setup table aliases		
		tabTable.setTableAlias("TR");
		colTable.setTableAlias("FR");
	
		// select * from <table1>
		statement = new SelectStatement(colTable, true);
		
		// Join Table2 and field for table2
		statement.from().leftJoin(tabTable);
		statement.select().column(tabTable.getColumn(DbfTableRegistryTable.DATABASE_TABLE_NAME), "MY_COLUMN_ALIAS");
		
	
		// Join Table3 and add field alias2
		tabTable.setTableAlias("TR2");
		statement.select().column(tabTable.getColumn(DbfTableRegistryTable.DATABASE_TABLE_NAME), "MY_OTHER_ALIAS");
		statement.from().rightJoin(tabTable);
		
		// Join Table3 and add field alias2
		tabTable.setTableAlias("TR3");
		statement.from().fullJoin(tabTable);
		
		// Join Table3 and add field alias2
		tabTable.setTableAlias("TR4");
		statement.from().crossJoin(tabTable);
		
		// Print results
		System.out.println("\n\n" + statement.getSQL());
	}
	
	/**
	 * Example: SELECT <alias1>.*, 
	 *                 <alias2>.field <field_alias1>
	 *                 <alias13>.field <field_alias2>
	 *            FROM <table1> alias1
	 *                 LEFT OUTER JOIN <table2> alias2
	 *                   ON <alias1>.<key> = <alias2>.<foreign key>
	 *                 CROSS JOIN <table4> alias5
	 *                   ON <alias1>.<key> = <alias5>.<foreign key>
	 */
	public void example5(int translatorType) {
		// DatabaseFacade.translatorTestingInit(translatorType);	
		SelectStatement statement;
		
		// Setup table aliases		
		tabTable.setTableAlias("TR");
		colTable.setTableAlias("FR");
	
		// select * from <table1>
		statement = new SelectStatement(colTable, true);
		
		// Join Table2 and field for table2
		statement.from().leftJoin(tabTable);
		statement.select().column(tabTable.getColumn(DbfTableRegistryTable.DATABASE_TABLE_NAME), "MY_COLUMN_ALIAS");
				
		// Join Table3 and add field alias2
		tabTable.setTableAlias("TR4");
		statement.from().crossJoin(tabTable);
		
		// Print results
		System.out.println("\n\n" + statement.getSQL());
	}
	
	/**
	 * Special with Hint!
	 * 
	 * Example: SELECT <alias1>.*, 
	 *                 <alias2>.field <field_alias1>
	 *                 <alias3>.field <field_alias2>
	 *            FROM <table1> alias1
	 *            	   INNER JOIN <table2> alias2
	 *                   ON <alias1>.<key> = <alias2>.<foreign key>
	 *                 LEFT OUTER JOIN <table3> alias3
	 *                   ON <alias1>.<key> = <alias3>.<foreign key>
	 *                 RIGHT JOIN <table4> alias4
	 *                   ON <alias1>.<key> = <alias3>.<foreign key>
	 *           WHERE <alias4>.<field> = 'TableRegistry'
	 */
	public void example6() {
		// DatabaseFacade.translatorTestingInit(translatorType);	
		SelectStatement statement;
		
		// Setup table aliases		
		colTable.setTableAlias("T1");
		tabTable.setTableAlias("T2");
		
	
		// select * from <table1>
		statement = new SelectStatement(colTable, true);
		
		// Join Table2 
		statement.from().join(tabTable);
		statement.select().column(tabTable.getColumn(DbfTableRegistryTable.DATABASE_TABLE_NAME), "MY_COLUMN_ALIAS");
				
		// Join Table3 
		tabTable.setTableAlias("T3");
		statement.from().leftJoin(tabTable);
		
		// Join Table4
		tabTable.setTableAlias("T4");
		statement.from().rightJoin(tabTable);
		
		Comparison comp = new Comparison();
		comp.alwaysTrue();
		statement.where(comp);
		comp = new Comparison();
		comp.equalToValue(tabTable.getColumn(DbfTableRegistryTable.DATABASE_TABLE_NAME), "TableRegistry");
		statement.where().and(comp);
		
		statement.select().hints().setOracleSelectHint("/*+ CACHE(" + tabTable.getTableName() + ") */");
		
		// Print results
		System.out.println("\n\n" + statement.getSQL());
	}
	
	/**
	 * Example: SELECT MAX(<alias1>.<field3>)
	 *            FROM <table1> <alias1>
	 */
	public void example7(int translatorType) {
		// DatabaseFacade.translatorTestingInit(translatorType);	
		SelectStatement statement;
		
		// Setup table aliases		
		colTable.setTableAlias("T1");

		// select * from <table1>
		statement = new SelectStatement(colTable);
		statement.select().functions().max(colTable.getColumn(DbfFieldRegistryTable.FIELD_NAME), "MAX_FIELD_NAME");

				
		// Print results
		System.out.println("\n\n" + statement.getSQL());
	}

	/**
	 * Example: SELECT <alias1>.<field1>, 
	 *                 MAX(<alias1>.<field3>) col_alias,
	 *                 <constant value> col_alias
	 *            FROM <table1> alias1
	 *            	   INNER JOIN <table2> alias2
	 *                   ON <alias1>.<key> = <alias2>.<foreign key>
	 *           WHERE <alias2>.<field> = 'TableRegistry'
	 *          GROUP BY <non-aggregate field and non-constant fields> // Generated
	 *          ORDER BY <alias1>.<field1>
	 */
	public void example8(int translatorType) {
		// DatabaseFacade.translatorTestingInit(translatorType);	
		SelectStatement statement;
		
		// Setup table aliases		
		colTable.setTableAlias("T1");
		tabTable.setTableAlias("T2");
	
		// select * from <table1>
		statement = new SelectStatement(colTable);
		Column field1 = colTable.getColumn(DbfFieldRegistryTable.FIELD_NAME);
		statement.select().column(field1);
		statement.select().functions().max(colTable.getColumn(DbfFieldRegistryTable.FIELD_NAME), "MAX_FIELD_NAME");
		statement.select().constant("just some constant value", "I_want_this_column");
		
		// Join Table2 
		statement.from().join(tabTable);
		
		Comparison comp = new Comparison();
		comp.equalToValue(tabTable.getColumn(DbfTableRegistryTable.DATABASE_TABLE_NAME), "TableRegistry");
		statement.where(comp);
		
		statement.orderBy().column(field1);
		
		// Print results
		System.out.println("\n\n" + statement.getSQL());
	}

	public static void main(String[] args) throws Exception {
		System.out.println("\n\n****************  ANSI SQL 92 ****************");
		DatabaseFacade ansiFacade = new DatabaseFacade(DatabaseFacade.ANSI_SQL_92);
		ExampleSelectStatements examples = new ExampleSelectStatements();
		examples.tabTable = new DbfTableRegistryTable(ansiFacade.getDatabaseHelper());
		examples.colTable = new DbfFieldRegistryTable(ansiFacade.getDatabaseHelper());
		examples.example6();
		System.out.println("\n\n****************  ORACLE ****************");
		DatabaseFacade oracleFacade = new DatabaseFacade(DatabaseFacade.ORACLE);
		examples = new ExampleSelectStatements();
		examples.tabTable = new DbfTableRegistryTable(oracleFacade.getDatabaseHelper());
		examples.colTable = new DbfFieldRegistryTable(oracleFacade.getDatabaseHelper());
		examples.example6();
		System.out.println("\n\n**************** MYSQL ****************");
		DatabaseFacade mySqlFacade = new DatabaseFacade(DatabaseFacade.MSSQL);
		examples = new ExampleSelectStatements();
		examples.tabTable = new DbfTableRegistryTable(mySqlFacade.getDatabaseHelper());
		examples.colTable = new DbfFieldRegistryTable(mySqlFacade.getDatabaseHelper());
		examples.example6();
		System.out.println("\n\n****************  MSSQL ****************");
		DatabaseFacade msSqlFacade = new DatabaseFacade(DatabaseFacade.MSSQL);
		examples = new ExampleSelectStatements();
		examples.tabTable = new DbfTableRegistryTable(msSqlFacade.getDatabaseHelper());
		examples.colTable = new DbfFieldRegistryTable(msSqlFacade.getDatabaseHelper());
		examples.example6();
		System.exit(0);
	}
}
